ERROR: ExecEvalAggref

Поиск
Список
Период
Сортировка
От eric soroos
Тема ERROR: ExecEvalAggref
Дата
Msg-id 90555128.1168357862@[4.42.179.151]
обсуждение исходный текст
Ответы Re: ERROR: ExecEvalAggref  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
I'm having trouble with an aggregate function under both 7.2.1 and 7.2.3.

Essentially, the aggreate function returns the last attribute value seen, so for
the example below, profiler(... a ...) = bar and profiler(... b...) = foo.

_date       a     b
1/1/03     foo   null
1/2/03       bar   baz
1/3/03     null     foo

For the most part it works properly, except that one of my installations
is having trouble.

This query fails:   (this is a minimal subset of a larger query)

update dl_profile set
        city= profiler(concat(dl_event._eventDate,dl_event.city))::text
        from dl_event where dl_event._donorNum='385'
        and dl_profile._donorNum='385'
        and dl_event._flDeleted='f'

ERROR:  ExecEvalAggref: no aggregates in this expression context


This query succeeds: (same query, different field)

update dl_profile set
        _outgoingSubject= profiler(concat(dl_event._eventDate,dl_event._outgoingSubject))::text
        from dl_event where dl_event._donorNum='385'
        and dl_profile._donorNum='385'
        and dl_event._flDeleted='f'

This query also succeeds, giving the expected values:

select profiler(concat(dl_event._eventDate,dl_event.city))::text as city,
    profiler(concat(dl_event._eventDate,dl_event._outgoingSubject))::text as outgoingSubject
    from dl_event where _donorNum='385'
    and dl_event._flDeleted='f';

   city    |            outgoingsubject
-----------+---------------------------------------
 Cambridge | ********* News: January 20th, 2003
(1 row)


These are the definitions of the functions that the aggregate relies on:


CREATE FUNCTION "datefromconcat" (text) RETURNS timestamp with time zone AS
'select substring($1 from 0 for (position(''|'' in $1)-1))::timestamp'
LANGUAGE 'sql' WITH ( iscachable, isstrict );

CREATE FUNCTION "valuefromconcat" (text) RETURNS text AS
'select substring($1 from (position(''|'' in $1)+1))'
LANGUAGE 'sql' WITH ( iscachable, isstrict );


CREATE FUNCTION "concat" (timestamp with time zone,text) RETURNS text AS
'select $1::text || ''|'' || $2'
LANGUAGE 'sql' WITH ( iscachable, isstrict );


CREATE FUNCTION "aggregateprofile" (text,text) RETURNS text AS
'select case when $2 is null then $1
when dateFromConcat($1) > dateFromConcat($2) then $1
else $2
   end' LANGUAGE 'sql' WITH ( iscachable, isstrict );


CREATE AGGREGATE profiler
    ( BASETYPE = text,
        SFUNC = aggregateprofile,
        STYPE = text,
        FINALFUNC = valuefromconcat );

Any ideas?



В списке pgsql-general по дате отправления:

Предыдущее
От: "ayhan"
Дата:
Сообщение: PgSPI -- How "experimental" is it ?
Следующее
От: "Daniel Schuchardt"
Дата:
Сообщение: own Function as Default VALUE